package dao.impl;

import dao.IDAO;
import dao.IFlightDAO;
import dao.tro.Airport;
import dao.tro.Flight;
import org.apache.log4j.Logger;
import sky.exc.DBException;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

public class HSQLDBFlightDAO extends IDAO implements IFlightDAO{
    private Connection connection=null;
    private final static Logger logs = Logger.getLogger(HSQLDBFlightDAO.class);

    public HSQLDBFlightDAO() {
        connection = HSQLDBDAOFactory.getConnection();
    }

    public void insert (Flight flight) throws DBException{
        try {
            //подтягиваем ид аэропорта отбытия по полученному имени
            String query = "SELECT Id FROM Airports WHERE name = ?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setString(1, flight.getDepartureAirport().getName());
            ResultSet resultSet = statement.executeQuery(query);
            resultSet.next();
            Long departureAirport = resultSet.getLong("Id");
            //подтягиваем ид аэропорта прибытия по имени
            statement.setString(1, flight.getDestinationAirport().getName());
            resultSet = statement.executeQuery(query);
            resultSet.next();
            Long destinationAirport = resultSet.getLong("Id");
            //вставляем полет в бд
            query = "INSERT INTO Flights VALUES (?, ?, ?, ?, ?)";
            statement = connection.prepareStatement(query);
            statement.setLong(1, flight.getId());
            statement.setLong(2, departureAirport);
            statement.setLong(3, destinationAirport);
            statement.setInt(4, flight.getGate());
            statement.setTimestamp(5, flight.getT());
            statement.executeUpdate(query);
        } catch (SQLException e) {
            logs.error("Ошибка вставки " + e);
        }
    }

    public void update (Flight flight) throws DBException{
        try {
            //подтягиваем ид аэропорта отбытия по имени
            String query = "SELECT Id FROM Airports WHERE Name=?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setString(1, flight.getDepartureAirport().getName());
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();
            Long departureAirportId = resultSet.getLong("Id");
            //подтягиваем  ид аэропорта прибытия по имени
            query = "SELECT Id FROM Airports WHERE Name=?";
            statement = connection.prepareStatement(query);
            statement.setString(1, flight.getDepartureAirport().getName());
            resultSet = statement.executeQuery();
            resultSet.next();
            Long destinationAirportId = resultSet.getLong("Id");
            //обновляем рейс
            query = "UPDATE Flights SET DepartureAirport = ?, DestinationAirport = ?, Gate = ?, t = ? WHERE Id = ?";
            statement = connection.prepareStatement(query);
            statement.setLong(5, flight.getId());
            statement.setLong(1, departureAirportId);
            statement.setLong(2, destinationAirportId);
            statement.setInt(3, flight.getGate());
            statement.setTimestamp(4, flight.getT());
            statement.executeUpdate(query);
        } catch (SQLException e) {
            logs.error("Ошибка изменения "+e);
        }
    }

    public void delete (long id) {
        super.delete(id);
    }

    public List<Flight> searchForFlightsFromAirport(String departureAirportName){
        List<Flight> flightList = new ArrayList<Flight>();
        try {
            //подтягиваем имя аэропорта отбытия
            String query = "SELECT Id FROM Airports WHERE Airports.Name = ?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setString(1, departureAirportName);
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();
            Long departureAirportId = resultSet.getLong("Id");
            //вытягиваем необходимые нам рейсы
            query = "SELECT Flights.Id, Flights.destinationAirport, Flights.Gate, Flights.t, Airports.Name AS AName " +
                    "FROM Flights INNER JOIN Airports ON Flights.destinationAirport=Airports.Id WHERE Flights.departureAirport= ?";
            PreparedStatement st = connection.prepareStatement(query);
            st.setLong(1, departureAirportId);
            resultSet = st.executeQuery();
            //заносим их в список
            int listIterator = 0;
            while (resultSet.next()) {
                flightList.add(new Flight());
                flightList.get(listIterator).setId(resultSet.getLong("Id"));
                flightList.get(listIterator).setDepartureAirport(new Airport());
                flightList.get(listIterator).getDepartureAirport().setId(departureAirportId);
                flightList.get(listIterator).getDepartureAirport().setName(departureAirportName);
                flightList.get(listIterator).setDestinationAirport(new Airport());
                flightList.get(listIterator).getDestinationAirport().setId(resultSet.getLong("destinationAirport"));
                flightList.get(listIterator).getDestinationAirport().setName(resultSet.getString("AName"));
                flightList.get(listIterator).setGate(resultSet.getInt("Gate"));
                listIterator++;
            }
        } catch (SQLException e) {
            logs.error("Ошибка выборки из БД(из) "+e);
        }

        return flightList;
    }

    public List<Flight> searchForFlightsToAirport(String destinationAirportName){
        List<Flight> flightList = new ArrayList<Flight>();
        try {
            //подтягиваем имя аэропорта прибытия
            String query = "SELECT Id FROM Airports WHERE Airports.Name = ?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setString(1, destinationAirportName);
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();
            Long destinationAirportId = resultSet.getLong("Id");
            //вытягиваем необходимые нам рейсы
            query = "SELECT Flights.Id, Flights.departureAirport, Flights.Gate, Flights.t, Airports.Name AS AName " +
                    "FROM Flights INNER JOIN Airports ON Flights.departureAirport=Airports.Id WHERE Flights.destinationAirport= ?";
            PreparedStatement st = connection.prepareStatement(query);
            st.setLong(1, destinationAirportId);
            resultSet = st.executeQuery();
            //заносим их в список
            int listIterator =0;
            while (resultSet.next()) {
                flightList.add(new Flight());
                flightList.get(listIterator).setId(resultSet.getLong("Id"));
                flightList.get(listIterator).setDepartureAirport(new Airport());
                flightList.get(listIterator).getDepartureAirport().setId(resultSet.getLong("departureAirport"));
                flightList.get(listIterator).getDepartureAirport().setName(resultSet.getString("AName"));
                flightList.get(listIterator).setDestinationAirport(new Airport());
                flightList.get(listIterator).getDestinationAirport().setId(destinationAirportId);
                flightList.get(listIterator).getDestinationAirport().setName(destinationAirportName);
                flightList.get(listIterator).setGate(resultSet.getInt("Gate"));
                listIterator++;
            }
        } catch (SQLException e) {
            logs.error("Ошибка выборки из БД(В) "+e);
        }
        return flightList;
    }

    public Flight showInformation(Long id) {
        Flight flight = new Flight();
        try {
            String query = "SELECT Airports.Name AS Name FROM Flights INNER JOIN Airports ON Flights.DepartureAirport=Airports.Id WHERE Flights.Id=?";
            PreparedStatement statement = connection.prepareStatement(query);
            statement.setLong(1, id);
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();
            String departureAirportName = resultSet.getString("Name");
            query = "SELECT Flights.departureAirport AS departureAirport, Flights.destinationAirport AS destinationAirport, Flights.Id, Flights.t, Flights.Gate, Airports.Name AS Name FROM Flights INNER JOIN Airports" +
                    " ON Airports.Id=Flights.destinationAirport WHERE Flights.Id=?";
            statement = connection.prepareStatement(query);
            statement.setLong(1, id);
            resultSet = statement.executeQuery();
            resultSet.next();
            flight.setDepartureAirport(new Airport());
            flight.getDepartureAirport().setId(resultSet.getLong("departureAirport"));
            flight.getDepartureAirport().setName(departureAirportName);
            flight.setId(resultSet.getLong("id"));
            flight.setDestinationAirport(new Airport());
            flight.getDestinationAirport().setId(resultSet.getLong("destinationAirport"));
            flight.getDestinationAirport().setName(resultSet.getString("Name"));
            flight.setGate(resultSet.getInt("Gate"));
            flight.setT(resultSet.getTimestamp("t"));
        } catch(SQLException e) {
            logs.info("Ошибка выборки "+e);
        }
        return flight;
    }

    public List<Flight> flightList(int flightCount) {
        List<Flight> flightList = new ArrayList<Flight>();
        try {
            //получим список аэропотов отбытия для всех рейсов
            String query = "SELECT Flights.Id, Airports.Name, Airports.Id AS AID FROM Flights INNER JOIN Airports " +
                    "ON Flights.departureAirport=Airports.Id";
            PreparedStatement statement = connection.prepareStatement(query);
            ResultSet departureResultSet = statement.executeQuery();
            //а тут уже список аэропортов прибытия и параметры каждого рейса
            query = "SELECT Flights.Id, Flights.destinationAirport, Flights.Gate, Flights.t, Airports.Name, Airports.Id AS AID " +
                    "FROM Flights INNER JOIN Airports ON Flights.destinationAirport=Airports.Id";
            PreparedStatement st = connection.prepareStatement(query);
            ResultSet destinationResultSet = st.executeQuery();
            //создаем список из [flightCount] аэропортов
            int listIterator =0;
            while ((departureResultSet.next())&&(destinationResultSet.next())&&(listIterator<flightCount)) {
                flightList.add(new Flight());
                flightList.get(listIterator).setId(destinationResultSet.getLong("Id"));
                flightList.get(listIterator).setDepartureAirport(new Airport());
                flightList.get(listIterator).getDepartureAirport().setId(departureResultSet.getLong("AID"));
                flightList.get(listIterator).getDepartureAirport().setName(departureResultSet.getString("Name"));
                flightList.get(listIterator).setDestinationAirport(new Airport());
                flightList.get(listIterator).getDestinationAirport().setId(destinationResultSet.getLong("AID"));
                flightList.get(listIterator).getDestinationAirport().setName(destinationResultSet.getString("Name"));
                flightList.get(listIterator).setGate(destinationResultSet.getInt("Gate"));
                flightList.get(listIterator).setT(destinationResultSet.getTimestamp("t"));
                listIterator++;
            }
        } catch (SQLException e) {
            logs.error("Ошибка в составлении списка аэропортов "+e);
        }
        return flightList;
    }

    public String getTableName() {
        return "Flights";
    }
}
